package org.adg.code.dao;

//import java.io.BufferedWriter;
import java.io.File;
//import java.io.FileOutputStream;

//import java.io.OutputStreamWriter;
//import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;




//import javax.xml.parsers.DocumentBuilderFactory;


import org.w3c.dom.*;
//import javax.xml.parsers.*;
//import javax.xml.transform.*;
//import javax.xml.transform.dom.*;
//import javax.xml.transform.stream.*;  

import org.adg.code.common.AdgProperties;
import org.adg.code.model.AdgProps;
import org.adg.code.model.ReturnResultSet;
import org.adg.code.model.SaveTemplateValues;
import org.adg.code.model.TemplateMultiValues;




public class TemplateDaoImpl implements TemplateDao {

	
	public ArrayList<Object> getTemplateOtions() {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		ReturnResultSet rrs=new ReturnResultSet();
		ReturnResultSet rrs1=new ReturnResultSet();
		ArrayList<Object> al=new ArrayList<Object>();
		try
		{
		Statement statement1 = con.createStatement();
		Statement statement2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		
		ResultSet rs1;
		rs1 = statement1.executeQuery("select description from report_type");
		rrs.setResultSet(rs1);
		
		ResultSet rs2;
		rs2= statement2.executeQuery("select description from ui_type");
		rrs1.setResultSet(rs2);
		
		
		
		
		al.add(rs1);	
		al.add(rs2);	
		
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		 
		
		return al;
	}

	
	public boolean saveTemplate(SaveTemplateValues stv[],TemplateMultiValues tmv[],int numofrows) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		
		int tfid=0;int rtypeid=0;int rptid=0;int uitypeid=0;int rpid=0;
		
		String keyword,rptypename="",uitype;
		

		try
		{
			String repname=stv[0].getrepname();
			String rtype=stv[0].getrtype();
			String tfn=stv[0].gettfn();
			
			Statement statement = con.createStatement();
			ResultSet rs1;
			rs1 = statement.executeQuery("select id from report_type where description=\'"+rtype+"\';");
			if(rs1.next())
			{
				rtypeid=Integer.parseInt(rs1.getString(1));
			}
			statement.close();
			
			statement=con.createStatement();
			rs1 = statement.executeQuery("insert into template_filename(description) values(\'"+tfn+"\') returning id;");
			if(rs1.next())
			{
				tfid=Integer.parseInt(rs1.getString(1));
			}
			statement.close();
			
			PreparedStatement pstat = null;
			String query=null;
							
			
			query = "insert into report(report_type_id,name,template_filename_id) values("+rtypeid+",?,"+tfid+")";
			pstat=null;
			pstat = con.prepareStatement(query);
			pstat.setString(1, repname);
			pstat.executeUpdate();
			
			
			int f=0;
			for(int i=1;i<=numofrows;i++)
			{
				
				keyword=stv[i].getKey();
				rptypename=stv[i].getrptn();
				uitype=stv[i].getuitype();
				
				statement=con.createStatement();
				rs1 = statement.executeQuery("insert into report_particular_type(name) values(\'"+rptypename+"\') returning id");
				if(rs1.next())
				{
					rptid=Integer.parseInt(rs1.getString(1));
				}
			
			
				query = "select id from ui_type where description=?";
				pstat=null;
				pstat = con.prepareStatement(query);
				pstat.setString(1, uitype);
				rs1=pstat.executeQuery(); 
				if(rs1.next())
				{
					uitypeid=Integer.parseInt(rs1.getString(1));
				}
			
			
				query = "insert into report_particulars(report_particulars_type_id,ui_type_id) values("+rptid+","+uitypeid+") returning id";
				pstat=null;
				pstat = con.prepareStatement(query);
				rs1=pstat.executeQuery();
				if(rs1.next())
				{
					rpid=Integer.parseInt(rs1.getString(1));
				}
			
				query = "insert into template(template_filename_id,report_particulars_id,keywordintemplate) values("+tfid+","+rpid+",\'"+keyword+"\')";
				pstat=null;
				pstat = con.prepareStatement(query);
				f=pstat.executeUpdate();
				statement.close();
				
				if(uitype.equalsIgnoreCase("Selectbox")||uitype.equalsIgnoreCase("Radiobutton"))
				{
					List<String> values=tmv[i].getList();
					for(int j=0;j<values.size();j++)
					{
						
						query="insert into option_values(report_particulars_id,values) values("+rpid+",\'"+values.get(j)+"\');";
						pstat = con.prepareStatement(query);
						pstat.executeUpdate();
						statement.close();
					}
					i=i+1;
				}
							
			}
		/*	try
			  {
			    DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();
			        DocumentBuilder docBuilder = builderFactory.newDocumentBuilder();
			        //creating a new instance of a DOM to build a DOM tree.
			        org.w3c.dom.Document doc = docBuilder.newDocument();
			        createXmlTree(doc,stv,numofrows);
			    
			  
			  }
			  catch(Exception e)
			  {
			    System.out.println(e);
			  }*/
			
			if(f!=0)
				return true;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		} 
		catch(DOMException se)
		{
			System.out.println("Save Proj Error : " + se);
		} 
		return false;
	}
/*	 public void createXmlTree(org.w3c.dom.Document doc,SaveTemplateValues stv[],int numofrows) throws Exception {
		//This method creates an element node
		 
		 	AdgProperties ADG=new AdgProperties();
	        AdgProps adg=ADG.getProperties();
	        
		 
		 	Element root = doc.createElement("Report");
	        //adding a node after the last child node of the specified node.
	        doc.appendChild(root);
	        String uitype="";
	        String rptname="";
		 for(int i=1;i<=numofrows;i++)
		 {
			
	       
			rptname=stv[i].getrptn().replaceAll(" ","_");
	        Element child1 = doc.createElement(rptname);
	        root.appendChild(child1);

	        Text text = doc.createTextNode(adg.getKeyidentifier()+stv[i].getKey()+adg.getKeyidentifier());
	        child1.appendChild(text);
	        uitype=stv[i].getuitype();
	       
	        if(uitype.equalsIgnoreCase("Selectbox")||uitype.equalsIgnoreCase("Radiobutton"))
			{
	        	i=i+1;
			}

		 }
	       
	       
	     //TransformerFactory instance is used to create Transformer objects. 
	        TransformerFactory factory = TransformerFactory.newInstance();
	        Transformer transformer = factory.newTransformer();
	       
	        transformer.setOutputProperty(OutputKeys.INDENT, "yes");

	        // create string from xml tree
	        StringWriter sw = new StringWriter();
	        StreamResult result = new StreamResult(sw);
	        DOMSource source = new DOMSource(doc);
	        transformer.transform(source, result);
	        String xmlString = sw.toString();

	        File d1=new File(adg.getTempsrc());
	         if(!(d1.exists()&&d1.isDirectory()))
	         {
	        	 File dir = new File(adg.getTempsrc());
	             dir.mkdirs();
	         }
	         File file = new File(adg.getTempsrc()+"\\"+stv[0].gettfn()+".xml");
	       	 BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file)));
	       	 bw.write(xmlString);
	       	 bw.flush();
	       	 bw.close();
	         
	 }*/

	
	public boolean tempNameExists(String tfname) {
		// TODO Auto-generated method stub
		 AdgProperties ADG=new AdgProperties();
         AdgProps adg=ADG.getProperties();
         
        
         File folder = new File(adg.getTempsrc());
         File[] listOfFiles = folder.listFiles();
        
         for (int i = 0; i < listOfFiles.length; i++) 
         {
             if (listOfFiles[i].isFile()) 
             {
             	if(listOfFiles[i].getName().matches(tfname+".xml"))
             	{
             		return true;
             	}
             }
         }
		
		return false;
	}


	
	public boolean repCatExists(String rname) {
		// TODO Auto-generated method stub
		DBConnection dbc =new DBConnection();
		ResultSet rs=null;
		try
		{
			Connection con = dbc.getConnection();
			Statement statement = con.createStatement();
			rs = statement.executeQuery("select * from report where name=\'"+rname+"\';");
			if(rs.next())
				return true;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		
		return false;
	}
}


